package com.css.fxfzypg.modules.rating.task.dao;

import cn.hutool.core.date.DateUtil;
import com.css.fxfzypg.modules.rating.expertRating.model.vo.KpRatingTaskByKrtIdVo;
import com.css.fxfzypg.modules.rating.task.model.entity.KpRatingTaskYpgTask;
import com.css.fxfzypg.modules.rating.task.model.vo.KpTaskManageNewStatusGlAreaDetailsVo;
import com.css.fxfzypg.modules.rating.task.model.vo.KpTaskManageNewStatusGlTaskDetailsVo;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public class KpRatingTaskYpgTaskRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void batchSave(List<KpRatingTaskYpgTask> ypgTasks) {
        String sql = "INSERT INTO kp_rating_task_ypg_task(id, ypg_task_id, jb_report_id, xb_report_id, xcdc_report_id, krt_id, create_time, xcdc_task_id) values(:id, :ypgTaskId, :jbReportId, :xbReportId, :xcdcReportId, :krtId, :createTime, :xcdcTaskId)";
        namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(ypgTasks.toArray()));
    }

    public List<KpRatingTaskYpgTask> list(KpRatingTaskYpgTask kpRatingTaskYpgTask) {
        StringBuilder sql = new StringBuilder("select * from kp_rating_task_ypg_task where 1=1 ");
        buildSelectConditionSql(kpRatingTaskYpgTask, sql);
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(KpRatingTaskYpgTask.class));
    }

    private static void buildSelectConditionSql(KpRatingTaskYpgTask kpRatingTaskYpgTask, StringBuilder sql) {
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getId())) {
            sql.append(" and id = '").append(kpRatingTaskYpgTask.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getYpgTaskId())) {
            sql.append(" and ypg_task_id = '").append(kpRatingTaskYpgTask.getYpgTaskId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getJbReportId())) {
            sql.append(" and jb_report_id = '").append(kpRatingTaskYpgTask.getJbReportId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getXbReportId())) {
            sql.append(" and xb_report_id = '").append(kpRatingTaskYpgTask.getXbReportId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getXcdcReportId())) {
            sql.append(" and xcdc_report_id = '").append(kpRatingTaskYpgTask.getXcdcReportId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getXcdcTaskId())) {
            sql.append(" and xcdc_task_id = '").append(kpRatingTaskYpgTask.getXcdcTaskId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getKrtId())) {
            sql.append(" and krt_id = '").append(kpRatingTaskYpgTask.getKrtId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(kpRatingTaskYpgTask.getCreateTime())) {
            sql.append(" and create_time = '").append(kpRatingTaskYpgTask.getCreateTime()).append("' ");
        }
    }

    public List<KpTaskManageNewStatusGlTaskDetailsVo> getKpGlTaskList(String krtId) {
        String sql = "SELECT id, ypg_task_id ypg_id,jb_report_id,xb_report_id,xcdc_report_id,xcdc_task_id xcdc_id from kp_rating_task_ypg_task where krt_id = '" + krtId + "' " + " order by create_time desc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpTaskManageNewStatusGlTaskDetailsVo.class));
    }

    public void deleteByKrtId(String krtId) {
        jdbcTemplate.update("delete from kp_rating_task_ypg_task where krt_id = '" + krtId + "'");
    }

    public Long count(KpRatingTaskYpgTask kpRatingTaskYpgTask) {
        StringBuilder sql = new StringBuilder("select count(*) from kp_rating_task_ypg_task where 1=1 ");
        buildSelectConditionSql(kpRatingTaskYpgTask, sql);
        return jdbcTemplate.queryForObject(sql.toString(), Long.class);
    }

    public KpRatingTaskYpgTask getById(String id) {
        try {
            return jdbcTemplate.queryForObject("select * from kp_rating_task_ypg_task where id = '" + id + "'", new BeanPropertyRowMapper<>(KpRatingTaskYpgTask.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public String getYpgNameByKrtytId(String krtytId) {
        try {
            return jdbcTemplate.queryForObject("select ypt.task_name from kp_rating_task_ypg_task krtyt left join ypg_preassess_task ypt on krtyt.ypg_task_id = ypt.id where krtyt.id = '" + krtytId + "' order by krtyt.create_time desc limit 1 offset 0", String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<KpTaskManageNewStatusGlAreaDetailsVo> getKpTaskManagerGlTaskList(String krtId) {
        String sql = String.format("SELECT\n" +
                "\tkrta.province_id,\n" +
                "\tkrta.krtyt_id,\n" +
                "\t\n" +
                "\t(\n" +
                "\tSELECT\n" +
                "\t\typt.task_name \n" +
                "\tFROM\n" +
                "\t\tkp_rating_task_ypg_task krtyt\n" +
                "\t\tLEFT JOIN ypg_preassess_task ypt ON krtyt.ypg_task_id = ypt.ID \n" +
                "\tWHERE\n" +
                "\t\tkrtyt.ID = krta.krtyt_id \n" +
                "\t) AS ypg_task_names,\n" +
                "\t( SELECT SUM ( krti3s.system_score ) * krta.area_ratio FROM kp_rating_task_index3_score krti3s WHERE krti3s.krta_id = krta.ID ) AS system_score,\n" +
                "\t( SELECT SUM ( kp_rating_task_item_score.last_score ) FROM kp_rating_task_item_score WHERE kp_rating_task_item_score.province_id = krta.province_id AND kp_rating_task_item_score.krt_id = '%s' ) AS last_score \n" +
                "FROM\n" +
                "\tkp_rating_task_area krta\n" +
                "\tLEFT JOIN kp_rating_task_ypg_task krtyt ON krta.krtyt_id = krtyt.ID \n" +
                "WHERE\n" +
                "\tkrtyt.krt_id = '%s'", krtId, krtId);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpTaskManageNewStatusGlAreaDetailsVo.class));
    }

    public List<KpRatingTaskByKrtIdVo.GlTask> getKpGlTaskExpert(String krtId, String expertId) {
       // String sql = "SELECT ktryt.*,ktryt.ypg_task_id ypg_id , ( SELECT status FROM kp_rating_task_expert krte LEFT JOIN kp_rating_task_expert_score_progress krtesp ON krte.ID = krtesp.krte_id WHERE krte.expert_id = ? AND krtesp.krtyt_id = ktryt.ID ) AS status, ( SELECT SUM(kti3s.system_score) FROM kp_rating_task_index3_score kti3s LEFT JOIN kp_rating_task_area kta ON kti3s.krta_id = kta.id WHERE kta.krtyt_id = ktryt.id ) + ( SELECT SUM(ktise.score) FROM kp_rating_task_index3_score_expert ktise WHERE ktise.krtyt_id = ktryt.id ) AS score FROM kp_rating_task_ypg_task ktryt WHERE ktryt.krt_id = ? ORDER BY ktryt.create_time DESC";
        String sql="SELECT\n" +
                "\tktryt.*,\n" +
                "\tktryt.ypg_task_id ypg_id,\n" +
                "\t(\n" +
                "\tSELECT\n" +
                "\t\tstatus \n" +
                "\tFROM\n" +
                "\t\tkp_rating_task_expert krte\n" +
                "\t\tLEFT JOIN kp_rating_task_expert_score_progress krtesp ON krte.ID = krtesp.krte_id \n" +
                "\tWHERE\n" +
                "\t\tkrte.expert_id = ?\n" +
                "\t\tAND krtesp.krtyt_id = ktryt.ID \n" +
                "\t) AS status,\n" +
                "\t(\n" +
                "\tSELECT COALESCE(SUM( kti3s.system_score ),0) as score  \n" +
                "\tFROM\n" +
                "\t\tkp_rating_task_index3_score kti3s\n" +
                "\t\tLEFT JOIN kp_rating_task_area kta ON kti3s.krta_id = kta.ID \n" +
                "\tWHERE\n" +
                "\t\tkta.krtyt_id = ktryt.ID  \n" +
                "\t) + ( SELECT COALESCE(SUM( ktise.score ),0) as score FROM kp_rating_task_index3_score_expert ktise WHERE ktise.krtyt_id = ktryt.ID   \tand ktise.expert_id= ? ) AS score \n" +
                "FROM\n" +
                "\tkp_rating_task_ypg_task ktryt \n" +
                "WHERE\n" +
                "\tktryt.krt_id = ?\n" +
                "ORDER BY\n" +
                "\tktryt.create_time DESC\n" +
                "\t";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskByKrtIdVo.GlTask.class), expertId,expertId, krtId);
    }

    public List<KpRatingTaskYpgTask> getByKryId(String krtId) {
        return jdbcTemplate.query("SELECT * from kp_rating_task_ypg_task WHERE krt_id = '" + krtId + "'", new BeanPropertyRowMapper<>(KpRatingTaskYpgTask.class));
    }

    public List<KpRatingTaskYpgTask> getByProvinceId(String provinceId, String krtId) {
        return jdbcTemplate.query("select DISTINCT t1.* from kp_rating_task_ypg_task t1 left join kp_rating_task_area t2 on t1.id = t2.krtyt_id where t2.province_id = '" + provinceId + "' and t1.krt_id = '" + krtId + "'", new BeanPropertyRowMapper<>(KpRatingTaskYpgTask.class));
    }
}
